package com.o2o.repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.springframework.context.annotation.Scope;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.mysql.jdbc.Statement;
import com.o2o.base.BaseRepository;
import com.o2o.model.topic.Category;
import com.o2o.model.user.User;
import com.o2o.model.user.UserProfile;
import com.o2o.util.TimeUtil;

@Repository
@Scope("prototype") 
public class UserRepository extends BaseRepository{
	
	public User getUserByEmailAccount(String emailAccount){
		String sql = "select * from v_user where email_account=? and status=1";
		return jdbcTemplate.queryForObject(sql,
				BeanPropertyRowMapper.newInstance(User.class), emailAccount);
	}
	
	public User getUserByUserName(String userName){
		String sql = "select * from v_user where name=? and status=1";
		return jdbcTemplate.queryForObject(sql,
				BeanPropertyRowMapper.newInstance(User.class), userName);
	}
	
	public List<Category> getAllCategorys(int status){
		String sql = "select * from v_category where status=?";
		return simpleJdbcTemplate.query(sql,BeanPropertyRowMapper.newInstance(Category.class), new Object[]{status});
	}
	
	public long saveOrUpdateUser(final String name,final String passwd,final int status,final String picture,final String emailAccount,final long time){
		final String sql = "insert into v_user(name,passwd,email_account,status,user_picture,time) values(?,?,?,?,?,?) on duplicate key update passwd=values(passwd)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			
			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); 
				ps.setString(1, name);
				ps.setString(2, passwd);
				ps.setString(3, emailAccount);
				ps.setInt(4, status);
				ps.setString(5, picture);
				ps.setLong(6, time/1000);
				return ps;
			}
		}, keyHolder);
		long generatedId = keyHolder.getKey().longValue();
		return generatedId;
	}
	
	public UserProfile getUserProfileByUserId(long userId){
		String sql = "select profile.* from v_user_profile profile where user_id=?";
		return simpleJdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(UserProfile.class), new Object[]{userId});
	}
	
	public boolean saveOrUpdateUserProfile(String userName,String phoneNumber,String userEmail,long userId,String fullAddressDetail,int areaCode,String address){
		String sql = "insert into v_user_profile(user_name,phone_number,user_email,user_id,full_address_detail,area_code,address) values(?,?,?,?,?,?,?) on duplicate key update " +
				"area_code=values(area_code),address=values(address),user_name=values(user_name),phone_number=values(phone_number),user_email=values(user_email),full_address_detail=values(full_address_detail)";
		int update = simpleJdbcTemplate.update(sql, new Object[]{userName,phoneNumber,userEmail,userId,fullAddressDetail,areaCode,address});
		System.out.println(update);
		if(update > 0) return true;
			else return false;
	}
	
	public long saveOrUpdateAddress(final int provinceCode,final int cityCode,final int areaCode,final String detail){
		final String sql = "insert into v_address(province_code,city_code,area_code,detail,add_time) values(?,?,?,?,?) on duplicate key update " +
				"province_code=values(province_code),city_code=values(city_code),area_code=values(area_code),detail=values(detail)";
		final String now = TimeUtil.getNow();
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection connection)
					throws SQLException {
				PreparedStatement ps = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); 
				ps.setInt(1, provinceCode);
				ps.setInt(2, cityCode);
				ps.setInt(3, areaCode);
				ps.setString(4, detail);
				ps.setString(5, now);
				return ps;
			}
		}, keyHolder);
		long generatedId = keyHolder.getKey().longValue();
		return generatedId;
	}
}
